package net.jsoft.pms.web.mysql.jcqd;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;

import net.jsoft.platform.core.util.platform.PlatformUtils;
import net.jsoft.platform.core.web.BaseController;
import net.jsoft.platform.security.entity.user.SysUser;
import net.jsoft.platform.security.repository.UserRepository;
import net.jsoft.pms.entity.plan.TaskJcqd;
import net.jsoft.pms.entity.versionplan.VersionPlan;
import net.jsoft.pms.mapper.jcqd.JcqdMapper;
import net.jsoft.pms.repository.taskjcqd.TaskJcqdRepository;
import net.jsoft.pms.repository.versionplan.VersionPlanRepository;
import net.sf.json.JSONObject;

@Controller
@RequestMapping("/jcqd")
public class JcqdController extends BaseController
{
    private String PREFIX = "/pms/mysql/jcqd/";

    @Autowired
    private JcqdMapper mapper;

    @Autowired
    private VersionPlanRepository repository;

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private TaskJcqdRepository taskJcqdRepository;

    /**
     * 跳转到计划管理首页
     */
    @RequestMapping("/list")
    public String list(Model model)
    {

        Sort sort = new Sort(Direction.DESC, "id");
        List<VersionPlan> allVersionPlans = repository.findAll(new Specification<VersionPlan>()
        {
            @Override
            public Predicate toPredicate(Root<VersionPlan> root, CriteriaQuery<?> query, CriteriaBuilder cb)
            {
                Predicate p = null;
                return p;
            }
        }, sort);

        model.addAttribute("allVersionPlans", allVersionPlans);
        return PREFIX + "list";
    }

    /**
     * 跳转到计划管理首页
     * @throws ParseException 
     */
    @RequestMapping("/listData")
    @ResponseBody
    public JSONObject listData(long id) throws ParseException
    {
        JSONObject obj = new JSONObject();
        List<SysUser> users = userRepository.findAll();
        StringBuffer sbfu = new StringBuffer();
        for (SysUser u : users)
        {
            sbfu.append("'").append(u.getName()).append("',");
        }
        String str = sbfu.toString();
        str = str.substring(0, str.length() - 1);
        VersionPlan thisVersion = repository.findOne(id);
        //        VersionPlan nextVersion = repository.findNextVersion(id);
        VersionPlan lastVersion = repository.findLastVersion(id);
        String start = PlatformUtils.getdate(PlatformUtils.getDayByadd(lastVersion.getPublishDate(), 1));
        String end = PlatformUtils.getdate(PlatformUtils.getDayByadd(thisVersion.getPublishDate(), 1));
        List<Map<String, Object>> jcqds = null;
        jcqds = mapper.getJcqdList(start, end, str);
        StringBuffer sbf = new StringBuffer();
        sbf.append("当前版本:").append(thisVersion.getVersion());
        sbf.append("  依赖核心版本").append(thisVersion.getDependOnVersion());
        sbf.append("  发布日期").append(PlatformUtils.getdate(thisVersion.getPublishDate()));
        sbf.append("  共计").append(jcqds.size()).append("个清单");
        sbf.append("<br/><hr/>");
        String wtms = null;
        String[] temp = null;
        String smwt = null;
        String group = null;
        String bz = null;
        String zt = null;
        String copyFlag = null;
        try
        {
            for (Map<String, Object> jcqd : jcqds)
            {

                String memo = (String) jcqd.get("MEMO");

                temp = memo.split("。修改说明：");
                wtms = temp[0];
                if (temp.length > 1 && temp[1].indexOf("修改人") > -1)
                {
                    temp = temp[1].split("修改人：");
                    smwt = "修改说明：" + temp[0];

                    if (temp.length > 1)
                    {
                        bz = "备注：" + temp[1];
                    }
                    else
                    {
                        bz = "";
                    }
                }
                else
                {
                    temp = temp[1].split("。备注：");
                    smwt = "修改说明：" + temp[0];
                    if (temp.length > 1)
                    {
                        bz = "备注：" + temp[1];
                    }
                    else
                    {
                        bz = "";
                    }

                    //                xgsm = "修改人：";
                }
                copyFlag = (String) jcqd.get("COPY_FLAG");
                // 0已同步,1未允许同步,2允许同步,4撤销可同步-byzsn
                switch (copyFlag)
                {
                case "0":
                    copyFlag = "已同步";
                    break;
                case "1":
                    copyFlag = "未允许同步";
                    break;
                case "2":
                    copyFlag = "允许同步";
                    break;
                case "4":
                    copyFlag = "撤销可同步";
                    break;
                case "5":
                    copyFlag = "实验室未集成";
                    break;
                }

                zt = (String) jcqd.get("JC_STATE");
                switch (zt)
                {
                case "0":
                    zt = "暂存";
                    break;
                case "1":
                    zt = "提交";
                    break;
                case "2":
                    zt = "撤销";
                    break;
                case "3":
                    zt = "已发布";
                    break;
                case "4":
                    zt = "已发布待同步";
                    break;
                case "5":
                    zt = "实验室未集成";
                    break;
                }

                group = (String) jcqd.get("GROUP_ID");
                switch (group)
                {
                case "32":
                    group = "技术平台";
                    break;
                case "33":
                    group = "申报缴税";
                    break;
                case "34":
                    group = "信息服务";
                    break;
                case "35":
                    group = "在线办税";
                    break;
                }

                sbf.append("清单编号：").append(jcqd.get("JC_MAINLIST_ID")).append("<br/>");
                sbf.append("清单状态：").append(zt).append("(").append(copyFlag).append(")").append("<br/>");
                sbf.append("录入时间：").append(PlatformUtils.getdate((Date) jcqd.get("CREATEDATE"))).append("<br/>");
                sbf.append("修改时间：").append(PlatformUtils.getdate((Date) jcqd.get("EDITDATE"))).append("<br/>");
                sbf.append("所属组别：").append(group).append("<br/>");
                sbf.append(wtms).append("<br/>");
                sbf.append(smwt).append("<br/>");
                //                        sbf.append(xgsm).append("<br/>");
                sbf.append(bz).append("<br/>");
                sbf.append("<hr/>");

            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        str = sbf.toString();

        obj.put("info", str);
        return obj;

    }

    /**
     * 跳转到计划管理首页
     * @throws ParseException 
     */
    @RequestMapping("/versionNote")
    @ResponseBody
    public JSONObject versionNote(long id) throws ParseException
    {
        JSONObject obj = new JSONObject();
        List<SysUser> users = userRepository.findAll();
        StringBuffer sbfu = new StringBuffer();
        for (SysUser u : users)
        {
            sbfu.append("'").append(u.getName()).append("',");
        }
        String str = sbfu.toString();
        str = str.substring(0, str.length() - 1);
        VersionPlan thisVersion = repository.findOne(id);
        VersionPlan lastVersion = repository.findLastVersion(id);
        String start = PlatformUtils.getdate(PlatformUtils.getDayByadd(lastVersion.getPublishDate(), 1));
        String end = PlatformUtils.getdate(PlatformUtils.getDayByadd(thisVersion.getPublishDate(), 1));
        List<Map<String, Object>> jcqds = null;
        jcqds = mapper.getJcqdVersoinNote(start, end, str);
        StringBuffer sbf = new StringBuffer();
        sbf.append("当前版本:").append(thisVersion.getVersion());
        sbf.append("  依赖核心版本").append(thisVersion.getDependOnVersion());
        sbf.append("  发布日期").append(PlatformUtils.getdate(thisVersion.getPublishDate()));
        sbf.append("  共计").append(jcqds.size()).append("个清单");
        sbf.append("<br/><hr/>");
        //        String wtms = null;
        //        String[] temp = null;
        //        String smwt = null;
        String group = null;
        //        String bz = null;
        String zt = null;
        //        String copyFlag = null;
        try
        {
            for (Map<String, Object> jcqd : jcqds)
            {

                //                String memo = (String) jcqd.get("MEMO");

                sbf.append("清单编号：").append(jcqd.get("JC_MAINLIST_ID")).append("<br/>");
                sbf.append("问题编号：").append(zt).append("<br/>");
                sbf.append("问题来源：").append(PlatformUtils.getdate((Date) jcqd.get("CREATEDATE"))).append("<br/>");
                sbf.append("用例名称：").append(PlatformUtils.getdate((Date) jcqd.get("EDITDATE"))).append("<br/>");
                sbf.append("问题描述：").append(group).append("<br/>");
                sbf.append("解决描述：").append(group).append("<br/>");
                sbf.append("<hr/>");

            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        str = sbf.toString();

        obj.put("info", str);
        return obj;

    }

    /**
     * 跳转到计划管理首页
     */
    @RequestMapping("/jcqdListPage")
    public String jcqdList(Model model)
    {
        Sort sort = new Sort(Direction.DESC, "id");
        List<VersionPlan> allVersionPlans = repository.findAll(new Specification<VersionPlan>()
        {
            @Override
            public Predicate toPredicate(Root<VersionPlan> root, CriteriaQuery<?> query, CriteriaBuilder cb)
            {
                Predicate p = null;
                return p;
            }
        }, sort);

        model.addAttribute("allVersionPlans", allVersionPlans);
        return PREFIX + "jcqdListPage";
    }

    @RequestMapping("/jcqdListData")
    @ResponseBody
    public JSONObject jcqdListData(int pageSize, int pageNumber, String sszb, String beginDate, String endDate, String optUser, String zt, String copyFlag, String jcqdId) throws ParseException
    {
        PageHelper.startPage(pageNumber, pageSize);
        SysUser user = null;

        if (StringUtils.isNotEmpty(sszb))
        {
            switch (sszb)
            {
            case "1":
                sszb = "'33'";
                break;
            case "2":
                sszb = "'35'";
                break;
            case "3":
                sszb = "'32','34'";
                break;
            }

        }

        if (StringUtils.isNotEmpty(optUser))
        {
            user = userRepository.findOne(Long.parseLong(optUser));
        }

        Page<Map<String, Object>> page = mapper.getJcqdListByParams(sszb, beginDate, endDate, user == null ? null : user.getName(), zt, copyFlag, jcqdId);

        JSONObject json = new JSONObject();
        json.put("rows", page.getResult()); //这里的 rows 和total 的key 是固定的 
        json.put("total", page.getTotal());
        return json;
    }

    @RequestMapping("/interfaceList")
    @ResponseBody
    public void interfaceList(String version, HttpServletRequest request, HttpServletResponse response)
    {

        HttpSession session = request.getSession();
        session.setAttribute("state", null);
        // 生成提示信息，
        response.setContentType("application/vnd.ms-excel");
        String codedFileName = null;
        OutputStream fOut = null;
        XSSFWorkbook workbook = null;
        try
        {
            String sql = " select distinct  p.name,p.optPerson, yw.ywfwid,p.earlyVersion,yw.dwfbzt,p.region,yw.ywfwmc , GROUP_CONCAT( distinct jc.qdbh   SEPARATOR ',')  qdbh ";
            sql += " from t_dzswj_plan p left join t_dzswj_plan_ywfwqd y on p.id = y.taskId left join t_dzswj_ywfwqdb  yw on y.ywfwqdId = yw.id ";
            sql += " left join t_dzswj_plan_jcqd jc on p.id = jc.taskId ";
            sql += " where p.earlyVersion = ? ";
            sql += " group by  p.name,p.optPerson,yw.ywfwid,p.earlyVersion,yw.dwfbzt,p.region,yw.ywfwmc ";

            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            list = this.getTemplate().queryForList(sql, version);

            codedFileName = URLEncoder.encode(version + "计划出厂接口各渠道反馈情况", "UTF-8");
            response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xlsx");
            //            // 产生工作簿对象

            String url = ResourceUtils.getURL("classpath:").getPath();
            url += "static/template/pms/jcqd/interfaceListTemplate.xlsx";
            File is = new File(url);
            FileInputStream input = new FileInputStream(is);
            workbook = new XSSFWorkbook(new BufferedInputStream(input));
            XSSFSheet sheet0 = workbook.getSheetAt(0);
            int i = 2;
            Map<String, Object> temp = null;
            for (int j = 0; j < list.size(); j++)
            {
                sheet0.shiftRows(j + i, j + i + 1, 1);
                temp = list.get(j);
                XSSFRow row = sheet0.createRow(i + j);// 创建一行
                row.createCell(0, CellType.STRING).setCellValue(j + 1);
                row.createCell(1, CellType.STRING).setCellValue(temp.get("ywfwid") == null ? "" : temp.get("ywfwid") + "");
                row.createCell(2, CellType.STRING).setCellValue(version);
                String value = (String) temp.get("dwfbzt");
                if ("1".equals(value))
                {
                    value = "已发布";
                }
                else if ("2".equals(value))
                {
                    value = "已作废";
                }
                else if ("3".equals(value))
                {
                    value = "新增待发布";
                }
                else if ("4".equals(value))
                {
                    value = "已修改待发布";
                }
                else if ("5".equals(value))
                {
                    value = "已作废待发布";
                }
                else
                {
                    value = "没有绑定业务服务清单";
                }
                row.createCell(3, CellType.STRING).setCellValue(value);
                row.createCell(4, CellType.STRING).setCellValue(temp.get("region") == null ? "内部优化" : temp.get("region") + "");
                //                row.createCell(5, CellType.STRING).setCellValue(temp.get("ywfwid") + "");
                row.createCell(6, CellType.STRING).setCellValue(temp.get("ywfwmc") == null ? "" : temp.get("ywfwmc") + "");
                row.createCell(7, CellType.STRING).setCellValue(temp.get("qdbh") == null ? "" : temp.get("qdbh") + "");
            }

            XSSFSheet sheet1 = workbook.getSheetAt(1);

            List<SysUser> users = userRepository.findAll();
            StringBuffer sbfu = new StringBuffer();
            for (SysUser u : users)
            {
                sbfu.append("'").append(u.getName()).append("',");
            }
            String str = sbfu.toString();
            str = str.substring(0, str.length() - 1);
            VersionPlan thisVersion = repository.findOneByVersion(version);
            //        VersionPlan nextVersion = repository.findNextVersion(id);
            VersionPlan lastVersion = repository.findLastVersion(thisVersion.getId());
            String start = PlatformUtils.getdate(PlatformUtils.getDayByadd(lastVersion.getPublishDate(), 1));
            String end = PlatformUtils.getdate(PlatformUtils.getDayByadd(thisVersion.getPublishDate(), 1));
            List<Map<String, Object>> jcqds = null;
            jcqds = mapper.getJcqdList(start, end, str);

            XSSFRow row = sheet1.getRow(0);
            row.createCell(1, CellType.STRING).setCellValue(thisVersion.getVersion());
            row.createCell(3, CellType.STRING).setCellValue(thisVersion.getDependOnVersion());
            row.createCell(5, CellType.STRING).setCellValue(PlatformUtils.getdate(thisVersion.getPublishDate()));
            row.createCell(7, CellType.STRING).setCellValue(jcqds.size());

            i = 2;
            for (int j = 0; j < jcqds.size(); j++)
            {
//                sheet1.shiftRows(j + i, j + i + 1, 1);
                temp = jcqds.get(j);
                row = sheet1.createRow(i + j);// 创建一行

                String copyFlag = (String) temp.get("COPY_FLAG");
                // 0已同步,1未允许同步,2允许同步,4撤销可同步-byzsn
                switch (copyFlag)
                {
                case "0":
                    copyFlag = "已同步";
                    break;
                case "1":
                    copyFlag = "未允许同步";
                    break;
                case "2":
                    copyFlag = "允许同步";
                    break;
                case "4":
                    copyFlag = "撤销可同步";
                    break;
                case "5":
                    copyFlag = "实验室未集成";
                    break;
                }

                String zt = (String) temp.get("JC_STATE");
                switch (zt)
                {
                case "0":
                    zt = "暂存";
                    break;
                case "1":
                    zt = "提交";
                    break;
                case "2":
                    zt = "撤销";
                    break;
                case "3":
                    zt = "已发布";
                    break;
                case "4":
                    zt = "已发布待同步";
                    break;
                case "5":
                    zt = "实验室未集成";
                    break;
                }

                String group = (String) temp.get("GROUP_ID");
                switch (group)
                {
                case "32":
                    group = "技术平台";
                    break;
                case "33":
                    group = "申报缴税";
                    break;
                case "34":
                    group = "信息服务";
                    break;
                case "35":
                    group = "在线办税";
                    break;
                }

                row.createCell(0, CellType.STRING).setCellValue(j + 1);
                row.createCell(1, CellType.STRING).setCellValue(group);
                row.createCell(2, CellType.STRING).setCellValue(temp.get("CREATOR") + "");
                row.createCell(3, CellType.STRING).setCellValue(temp.get("JC_MAINLIST_ID") + "");
                row.createCell(4, CellType.STRING).setCellValue(zt + "(" + copyFlag + ")");
                row.createCell(5, CellType.STRING).setCellValue(PlatformUtils.getdate((Date) temp.get("CREATEDATE")));
                row.createCell(6, CellType.STRING).setCellValue(PlatformUtils.getdate((Date) temp.get("EDITDATE")));
                row.createCell(7, CellType.STRING).setCellValue(temp.get("MEMO") + "");
            }

            fOut = response.getOutputStream();
            workbook.write(fOut);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if (fOut != null)
                {
                    fOut.flush();
                    fOut.close();
                }
                workbook.close();
            }
            catch (IOException e)
            {
            }
            session.setAttribute("state", "open");
        }

    }

    @RequestMapping("/editTask")
    public String editTask(String id, Model model)
    {
        Map<String, Object> jcqd = mapper.getJcqdByQdbh(id);
        model.addAttribute("jcqd", jcqd);
        String sql = " select y.id,y.taskId,p.name ,p.type , p.earlyStart,p.earlyFinish,p.lateStart,p.lateFinish,p.earlyVersion,p.lateVersion,p.versionDate,u.fullName,p.notes,p.problemId from t_dzswj_plan_jcqd y left join t_dzswj_plan p on y.taskId = p.id join t_security_user u on p.optPerson =  u.id  where  y.qdbh = ? ";
        List<Map<String, Object>> taskIds = this.getTemplate().queryForList(sql, id);
        model.addAttribute("taskIds", taskIds);
        return PREFIX + "editTask";
    }

    @RequestMapping("/saveTask")
    @ResponseBody
    public String saveTask(String jcqd, long id)
    {
        TaskJcqd taskJcqd = new TaskJcqd();
        taskJcqd.setQdbh(jcqd);
        taskJcqd.setTaskId(id);
        taskJcqdRepository.save(taskJcqd);
        return "ok";
    }

    @RequestMapping("/delTask")
    @ResponseBody
    public String saveTask(long id)
    {
        taskJcqdRepository.delete(id);
        return "ok";
    }

    @RequestMapping("/view")
    public String view(String id, Model model)
    {
        Map<String, Object> jcqd = mapper.getJcqdByQdbh(id);
        model.addAttribute("jcqd", jcqd);
        return PREFIX + "view";
    }

}
